package cn.xiaolang.function.connection;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

/**
 * 数据库存储过程操作
 * 
 * @author fuhw
 * @date 2017年12月1日
 */
public class DbProcedureOperateHelper {
	
	private static final Logger log = LoggerFactory.getLogger(DbProcedureOperateHelper.class);
	
	private static Connection connectionSql;
	static {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "root";
		try {
			Class.forName(driver);
			connectionSql = DriverManager.getConnection(url, user, password);
			if (!connectionSql.isClosed())
				log.info("Succeeded connecting to the Database! url:{}", url);
		} catch (ClassNotFoundException e) {
			log.debug("failed load db driber :{}", driver);
			e.printStackTrace();
		} catch (SQLException e) {
			log.debug("failed connecting to the Database! url:{},user:{},password:{}", url, user, password);
			e.printStackTrace();
		}
	}

	// @formatter:off
	/**
	 * 调用存储过程
	 * 
	 * @param procName
	 *            存储过程名称
	 * @param params
	 *            Map<参数索引值:索引值从1开始,参数对应的数据库字段类型 :java.sql.Types>
	 * @return CallableStatement 如果有参数可以从CallableStatement中获取,否则的话返回null
	 * @throws SQLException
	 * 
	 * <p>存储过程示例：</p>
	 * <div> 创建：create proceduce get_usercount(out usercount int,out rolecount int);</div>
	 * <div> BEGIN </div> 
	 * <div> SELECT count(0) FROM sys_user INTO usercount; </div> 
	 * <div> SELECT count(0) FROM sys_role INTO rolecount; </div>
	 * <div> END </div> 
	 * <div> 调用：call get_usercount(@usercount,@rolecount); </div>
	 */
	// @formatter:on
	public static CallableStatement callProc(String procName, Map<Integer, Integer> params) throws SQLException {
		Assert.hasText(procName, "the procName must be null");
		Assert.isTrue(!procName.endsWith("()"), "the procName can't endwith '()'");
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("{ call ").append(procName).append("(");
		if (params != null && !params.isEmpty()) {
			int paramCount = params.size();
			String[] placeholders = new String[paramCount];
			Arrays.fill(placeholders, 0, paramCount, "?");
			String paramPlaceHodlers = StringUtils.arrayToCommaDelimitedString(placeholders);
			sBuffer.append(paramPlaceHodlers);
		}
		sBuffer.append(") }");
		log.info("call procedure -> name: {},sql:{}", procName, sBuffer.toString());
		log.info("call procedure -> params:{}", params);
		CallableStatement prepareCall = null;
		prepareCall = connectionSql.prepareCall(sBuffer.toString());
		if (params != null && !params.isEmpty()) {
			for (Entry<Integer, Integer> param : params.entrySet()) {
				prepareCall.registerOutParameter(param.getKey(), param.getValue());
			}
		}
		prepareCall.execute();
		return prepareCall;
	}

	@Test
	public void demo() throws SQLException {
		Map<Integer, Integer> params = new LinkedHashMap<>();
		params.put(1, Types.INTEGER);
		params.put(2, Types.INTEGER);
		CallableStatement temp = callProc("get_usercount", params);
		System.err.println("usercount = > " + temp.getInt(1));
		System.err.println("rolecount = > " + temp.getInt(2));
	}
}
